package nci60.administration
import groovy.sql.Sql
import org.codehaus.groovy.grails.commons.ConfigurationHolder as CH

/**
 * This class is used for creating the sql files and uploading the data to the database.
 * Therefore it creates sql insert statements with a maximal number of entries and writes them to a file.
 * If wanted, the file is uploaded to the database<p />
 * This class is not mapped by GORM and therefore placed in the controller section.<p />
 * <b>See Also:</b> {@link helper.UploadService}
 *
 * @autor <a href="mailto:Florian.J.Auer@googlemail.com">Florian J. Auer</a>
*/
class SQLUploader {
	
    /**
     * Writer in which the sql statements are written
     */
    BufferedWriter sqlFile
    
    /**
     * Output file in which the writer writes
     */
    File sqlFileName
    
    /**
     * Default beginning of an insert statement
     */
    String insertStatement
    
    /**
     * Last line of data, which is passed to this object <p />
     * Can be <b>null</b> if data is allready written to the file or no data was passed yet
     */
    String lastLine


    /**
     * Counts with every passed data until the maximal number of lines ({@link #maxNumberOfLines}) are reached, then new insert statement is written and counter reset to 0
     */
    int counter = 0
    
    /**
     * Counts every line
     */
    int lineNumber = 1

    /**
     * Maximal number of lines, before new insert statement is written. <p />
     * This equals the number of datasets are maximaly inserted in one statement.
     */
    int maxNumberOfLines = 1000



     /**
     * Constructs a new object for generating SQL upload
     * @param sqlFile           File in which the statements will be written
     * @param tableName         Name of the SQL table in which the data will be inserted
     * @param columnNames       A ordered list of the names of the attributes of the table
     * @param maxNumberOfLines  Sets the maximal number of lines
     */
    public SQLUploader(File sqlFile, String tableName, List columnNames, int maxNumberOfLines) {
         this(sqlFile, tableName, columnNames)
     }

    /**
     * Constructs a new object for generating SQL upload
     * @param sqlFile           File in which the statements will be written
     * @param tableName         Name of the SQL table in which the data will be inserted
     * @param columnNames       A ordered list of the names of the attributes of the table
     */
    public SQLUploader(File sqlFile, String tableName, List columnNames) {
        this.sqlFileName = sqlFile
        this.sqlFile =  new BufferedWriter(new FileWriter(sqlFile))
        this.insertStatement =  "INSERT INTO "+tableName+" "+columnNames.toString().replaceAll('\\[','(').replaceAll('\\]',')')+" VALUES"
    }

    /**
     * Sets the new maximal number of inserted datasets
     * @param maxNumberOfLines New value
     */
    public void setMaxLineNr(int maxNumberOfLines){
        this.maxNumberOfLines = maxNumberOfLines
    }

    /**
     * Passing data to the SQL Uploader
     * @param values A list of data, which should be uploaded. The order has do be the same as the order of the column names specified in the constructor!
     */
    public void leftShift(List values){
        if(counter==0){
            sqlFile.write(insertStatement+'\n')
        }
        if(this.lastLine!=null) sqlFile.write(this.lastLine)
        this.lastLine = '('+values.toString().replaceAll('\\[','').replaceAll('\\]','')+')'
        counter++
        if(counter==maxNumberOfLines){
            if(lineNumber!=1) sqlFile.write(';\n')
            counter = 0
        }else{
            if(lineNumber!=1) sqlFile.write(',\n')
        }
        lineNumber++
    }


    /**
     * Has to be called, if no data has to be added anymore <p />
     * Unwritten data will be written and the line closed with a semicolon. <p />
     * This method is similar to the finalize method, but predictable because it is not dependent on the Garbage Collector. 
     */
    public void finish(){
        if(this.lastLine!=null) {
            sqlFile.write(this.lastLine+';\n')
            this.lastLine=null
            sqlFile.close()
        }
    }

    
    /**
     * Finishes writing of the data and uploads it to the database
     * @return 0 if uploading was successfull
     */
    public int upload(){
        finish()
        int exitValue = 0
//            def mySQLcommand = ["mysql","-u",CH.config.dataSource.username,"-p"+CH.config.dataSource.password,"-S","/opt/lampp/var/mysql/mysql.sock","-D","NCI60","-e \\. "+sqlFileName]
//            def command = ["mysql","-u",CH.config.dataSource.username,"-p"+CH.config.dataSource.password,"-S",socket,"-D"+CH.config.dataSource.url.replaceAll('jdbc:mysql://localhost/',''),"< "+sqlFileName]
        def user = CH.config.dataSource.username
        def pass = CH.config.dataSource.password
        def dbName = CH.config.dataSource.url.replaceAll('jdbc:mysql://localhost/','')

        def command = ["mysql","-u",user,"-p"+pass,"-S","/opt/lampp/var/mysql/mysql.sock","-D",dbName,"-e \\. "+sqlFileName]
        def process = command.execute()
        process.waitFor()
        exitValue = process.exitValue()
        
        return exitValue
    }
}

